package com.zsrt.oa.user.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zsrt.oa.user.dao.UserDao;
import com.zsrt.oa.user.entity.UserEntity;
import com.zsrt.utils.DBUtils;

/**
 * 用户持久层的实现类
 * @author 罗智壕-13424431416
 *
 * 2017年6月8日
 */
public class UserDaoImpl implements UserDao {
	
	/**
	 * 用户注册判断
	 * @return
	 */
	@Override
	public int registerGudge(UserEntity entity) throws SQLException{
		int counter = 0;
		Connection con = DBUtils.getMessage();
		String sql = "SELECT COUNT(USER_ID) AS RESULT"
				+ " FROM TKT_USER_T WHERE USER_PHONE = ?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, entity.getUserPhone());
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
			counter = rs.getInt("RESULT");
		}
		
		DBUtils.closeResources(con, ps, rs);
		return counter;
	}
	
	/**
	 * 用户注册
	 * @param entity
	 * @return
	 * @throws SQLException
	 */
	@Override
	public int userAdd(UserEntity entity) throws SQLException {
		int counter = 0;
		Connection con = DBUtils.getMessage();
		String sql = "INSERT INTO TKT_USER_T (USER_NAME,USER_PASSWORD,USER_PHONE,CREATE_TIME) "
				+ " VALUES(?,MD5(?),?,SYSDATE())";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, entity.getUserName());
		ps.setString(2, entity.getUserPassword());
		ps.setString(3, entity.getUserPhone());
		
		counter = ps.executeUpdate();
		
		DBUtils.closeResources(con, ps);
		return counter;
	}
	
	/**
	 * 用户登录
	 * @param entity
	 * @return
	 * @throws SQLException
	 */
	@Override
	public int userLogin(UserEntity entity) throws SQLException {
		int counter = 0;
		Connection con = DBUtils.getMessage();
		String sql = "SELECT COUNT(USER_ID) AS RESULT FROM TKT_USER_T WHERE "
				+ " USER_PHONE = ? AND "
				+ " USER_PASSWORD = MD5(?)";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, entity.getUserPhone());
		ps.setString(2, entity.getUserPassword());
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
			counter = rs.getInt("RESULT");
		}
		
		DBUtils.closeResources(con, ps, rs);
		return counter;
	}
	
	/**
	 * 当前用户信息
	 * @param userPhone
	 * @return
	 * @throws SQLException
	 */
	@Override
	public List<UserEntity> myMessage(String userPhone) throws SQLException {
		List<UserEntity> list = new ArrayList<UserEntity>();
		Connection con = DBUtils.getMessage();
		String sql = "SELECT "
					+ "	U.USER_NAME AS userName,"
					+ " U.USER_PHONE AS userPhone,"
					+ " U.USER_BALANCE AS userBalance,"
					+ " U.USER_INTERGRAL AS userIntergral,"
					+ " U.USER_LEVEL AS userLevel,"
					+ " U.CREATE_TIME AS createTime"
				+ " FROM TKT_USER_T U "
				+ " WHERE U.USER_PHONE = ?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, userPhone);
		ResultSet rs = ps.executeQuery();
		
		if(rs.next()){
			UserEntity entity = new UserEntity();
			entity.setUserName(rs.getString("userName"));
			entity.setUserPhone(rs.getString("userPhone"));
			entity.setUserBalance(rs.getDouble("userBalance"));
			entity.setUserIntegral(rs.getDouble("userIntergral"));
			entity.setUserLevel(rs.getString("userLevel"));
			entity.setCreateTime(rs.getString("createTime"));
			list.add(entity);
		}
		
		DBUtils.closeResources(con, ps, rs);
		return list;
	}
	
	/**
	 * 修改用户金额(充值)
	 * @param entity
	 * @return
	 * @throws SQLException
	 */
	@Override
	public int updateUserMoney(UserEntity entity) throws SQLException {
		int counter = 0;
		Connection con = DBUtils.getMessage();
		String sql = "UPDATE TKT_USER_T SET USER_BALANCE = USER_BALANCE + ? "
				+ " WHERE USER_PHONE = ?";
		PreparedStatement ps = con.prepareStatement(sql);
		
		ps.setDouble(1, entity.getUserBalance());
		ps.setString(2, entity.getUserPhone());
		
		counter = ps.executeUpdate();
		
		DBUtils.closeResources(con, ps);
		return counter;
	}
	
	/**
	 * 修改用户金额(预购)
	 * @param entity
	 * @return
	 * @throws SQLException
	 */
	@Override
	public int updateUserMoney(String price,UserEntity entity) throws SQLException {
		int counter = 0;
		Connection con = DBUtils.getMessage();
		String sql = "UPDATE TKT_USER_T SET USER_BALANCE = USER_BALANCE - ? "
				+ " WHERE USER_PHONE = ?";
		PreparedStatement ps = con.prepareStatement(sql);
		
		ps.setDouble(1, Double.valueOf(price));
		ps.setString(2, entity.getUserPhone());
		
		counter = ps.executeUpdate();
		
		DBUtils.closeResources(con, ps);
		return counter;
	}
	
	/**
	 * 修改用户级别
	 * @param entity
	 * @return
	 * @throws SQLException
	 */
	@Override
	public int updateUserLevel(UserEntity entity) throws SQLException {
		int counter = 0;
		Connection con = DBUtils.getMessage();
		String sql = "UPDATE TKT_USER_T SET USER_LEVEL = ? "
				+ " WHERE USER_PHONE = ?";
		PreparedStatement ps = con.prepareStatement(sql);
		
		ps.setString(1, entity.getUserLevel());
		ps.setString(2, entity.getUserPhone());
		
		counter = ps.executeUpdate();
		
		DBUtils.closeResources(con, ps);
		return counter;
	}
	
	/**
	 * 返回用户的级别
	 * @param userPhone
	 * @return
	 * @throws SQLException
	 */
	@Override
	public String returnUserLevel(String userPhone) throws SQLException {
		Connection con = DBUtils.getMessage();
		String sql = "select VIP_NAME as vipName from tkt_vip_t where vip_money != 0 "
				+ " and VIP_MONEY<=(select sum(RC_MONEY) "
				+ " from tkt_recharge_t where USER_ID = ?) "
				+ " order by VIP_ID desc limit 1";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, userPhone);
		ResultSet rs = ps.executeQuery();
		
		if(rs.next()){
			userPhone = rs.getString("vipName");
		}
		
		DBUtils.closeResources(con, ps);
		return userPhone;
	}
	
	/**
	 * 返回用户的折扣
	 * @param userPhone
	 * @return
	 * @throws SQLException
	 */
	@Override
	public int returnUserDiscount(String userPhone) throws SQLException {
		Connection con = DBUtils.getMessage();
		String sql = "select VIP_DISCOUNT as vipDiscount from tkt_vip_t where vip_money != 0 "
				+ " and VIP_MONEY<=(select sum(RC_MONEY) "
				+ " from tkt_recharge_t where USER_ID = ?) "
				+ " order by VIP_ID desc limit 1";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, userPhone);
		ResultSet rs = ps.executeQuery();
		int vipDiscount = 0;
		if(rs.next()){
			vipDiscount = rs.getInt("vipDiscount");
		}
		
		DBUtils.closeResources(con, ps);
		return vipDiscount;
	}
	
	/**
	 * 修改用户积分
	 * @param entity
	 * @return
	 * @throws SQLException
	 */
	@Override
	public int updateUserintegral(UserEntity entity) throws SQLException {
		int counter = 0;
		Connection con = DBUtils.getMessage();
		String sql = "UPDATE TKT_USER_T SET USER_INTERGRAL = USER_INTERGRAL + ? "
				+ " WHERE USER_PHONE = ?";
		PreparedStatement ps = con.prepareStatement(sql);
		
		ps.setDouble(1, entity.getUserIntegral());
		ps.setString(2, entity.getUserPhone());
		
		counter = ps.executeUpdate();
		
		DBUtils.closeResources(con, ps);
		return counter;
	}

}
